﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ZhimeiSoft.CharmManager.Server.DAL
{
    public class ScheduleDAL_Const
    {
        /// <summary>
        /// 插入提醒表记录
        /// </summary>
        public static readonly string SQL_INSERT_SCHEDULE = @"INSERT INTO SCHEDULE (SALONID ,SCHEDULENAME ,SCHEDULETIME ,SYSTIME ,STATE ,SPLANSTARTTIME ,SPLANENDTIME,
            SPLANTIMESTATE ,SPLANMEMO ,SCHEDULETYPE ,SPLANEMPLOYEE)   
            VALUES (@SALONID ,@SCHEDULENAME ,@SCHEDULETIME ,@SYSTIME ,@STATE ,@SPLANSTARTTIME ,@SPLANENDTIME ,@SPLANTIMESTATE ,@SPLANMEMO ,@SCHEDULETYPE ,@SPLANEMPLOYEE)";
        /// <summary>
        /// 删除提醒表记录
        /// </summary>
        public static readonly string SQL_DELETE_SCHEDULE = @"DELETE FROM SCHEDULE WHERE SCHEDUEID = @SCHEDUEID";
        /// <summary>
        /// 更新提醒表记录
        /// </summary>
        public static readonly string SQL_UPDATE_SCHEDULE = @"UPDATE SCHEDULE SET SALONID = @SALONID_NEW,SCHEDULENAME = @SCHEDULENAME_NEW,SCHEDULETIME = @SCHEDULETIME_NEW,SYSTIME = @SYSTIME_NEW,STATE = @STATE_NEW
                        ,SPLANSTARTTIME = @SPLANSTARTTIME_NEW,SPLANENDTIME = @SPLANENDTIME_NEW,SPLANTIMESTATE = @SPLANTIMESTATE_NEW,SPLANMEMO = @SPLANMEMO_NEW,SCHEDULETYPE = @SCHEDULETYPE_NEW,SPLANEMPLOYEE = @SPLANEMPLOYEE_NEW
                    WHERE SCHEDUEID = @SCHEDUEID ";

        /// <summary>
        /// 插入会员提醒记录
        /// </summary>
        public static readonly string SQL_INSERT_SCHEDULECUSTOM = @"INSERT INTO SCHEDULECUSTOM (CUSTOMID, SCALLDATE, SMEMO, SCHEDULEID, SBESPEAKSTATE) VALUES (@CUSTOMID, @SCALLDATE, @SMEMO, @SCHEDULEID, @SBESPEAKSTATE)";
        /// <summary>
        /// 删除会员提醒记录
        /// </summary>
        public static readonly string SQL_DEL_SCHEDULECUSTOM = "DELETE FROM SCHEDULECUSTOM WHERE CUSTOMID = @CUSTOMID AND SCHEDULEID = @SCHEDULEID AND SCALLDATE = @SCALLDATE AND SBESPEAKSTATE = @SBESPEAKSTATE";

        public static readonly string SQL_QUERY_SCHEDULE_STATE = @"SELECT SCHEDUEID ,SALONID ,SCHEDULENAME ,SCHEDULETIME ,SYSTIME ,STATE ,SPLANSTARTTIME ,SPLANENDTIME
                    ,SPLANTIMESTATE ,SPLANMEMO ,SCHEDULETYPE ,SPLANEMPLOYEE  FROM SCHEDULE WHERE STATE = @STATE ";

        /// <summary>
        /// 根据用户ID，和状态查询提醒信息
        /// </summary>
        public static readonly string SQL_QUERY_SCHEDULE_CUSTOMID = @"SELECT  A.*, B.* FROM SCHEDULE A, SCHEDULECUSTOM B, CUSTOMINFO C WHERE A.SCHEDUEID = B.SCHEDULEID AND B.CUSTOMID = @CUSTOMID AND A.STATE = @STATE ";

        /// <summary>
        /// 根据条件获取SCHEDULEID.
        /// </summary>
        public static readonly string SQL_QUERY_SCHEDULE_GETSCHEDULEID = @"SELECT SCHEDUEID  FROM SCHEDULE  WHERE SALONID = @SALONID AND SCHEDULENAME = @SCHEDULENAME AND SCHEDULETIME = @SCHEDULETIME AND SYSTIME = @SYSTIME AND STATE = @STATE 
            AND SPLANSTARTTIME = @SPLANSTARTTIME AND SPLANENDTIME = @SPLANENDTIME AND SPLANTIMESTATE = @SPLANTIMESTATE AND SCHEDULETYPE = @SCHEDULETYPE AND SPLANEMPLOYEE = @SPLANEMPLOYEE ";

        #region 预约信息
        /// <summary>
        /// 根据用户ID预约当前月分查询
        /// </summary>
        public static readonly string SQL_QUERY_SCHEDULECUSTOM_DATETIMECUSTOMID = @"SELECT A.*, B.*, C.CNAME + ' ' + CONVERT(varchar(100), B.SCALLDATE, 24) AS DISPVALUE
	            FROM SCHEDULE A, SCHEDULECUSTOM B, CUSTOMINFO C WHERE C.CUSTOMID = B.CUSTOMID AND A.SCHEDUEID = B.SCHEDULEID AND  B.SCALLDATE BETWEEN @BEGINTIME AND @ENDTIME AND B.CUSTOMID = @CUSTOMID";
        /// <summary>
        /// 查询当前月分的预约记录
        /// </summary>
        public static readonly string SQL_QUERY_SCHEDULE_DATETIME_MONTH = @"SELECT A.*, B.*, C.*, C.CNAME + ' ' + CONVERT(varchar(100), B.SCALLDATE, 24) AS DISPVALUE FROM SCHEDULE A, SCHEDULECUSTOM B, CUSTOMINFO C WHERE A.SCHEDUEID = B.SCHEDULEID AND B.CUSTOMID = C.CUSTOMID AND YEAR(B.SCALLDATE) = @YEAR
                            AND MONTH(B.SCALLDATE) = @MONTH";
        /// <summary>
        /// 查询当前日的预约记录
        /// </summary>
        public static readonly string SQL_QUERY_SCHEDULE_DATETIME_DAY = @"SELECT A.*, B.*, C.*, C.CNAME + ' ' + CONVERT(varchar(100), B.SCALLDATE, 24) AS DISPVALUE FROM SCHEDULE A, SCHEDULECUSTOM B, CUSTOMINFO C WHERE A.SCHEDUEID = B.SCHEDULEID AND B.CUSTOMID = C.CUSTOMID AND YEAR(B.SCALLDATE) = @YEAR
                            AND MONTH(B.SCALLDATE) = @MONTH AND DAY(B.SCALLDATE) = @DAY";
        #endregion

        #region 提醒信息
        /// <summary>
        /// 根据状态以及提醒的开始年月查询提醒信息
        /// </summary>
        public static readonly string SQL_QUERY_SCHEDULECALL_MONTH_STATE = @"SELECT * FROM SCHEDULE WHERE YEAR(SPLANSTARTTIME) = @YEAR AND MONTH(SPLANSTARTTIME) = @MONTH ";
        #endregion
    }
}
